<?php

class Client {

    public static function getCustomerList($page, $dataPerPage) {
        $sql = "SELECT *
            FROM client
            WHERE DeleteDate IS NULL 
            ORDER BY CompanyName,CreateDate DESC";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $maxPage = ceil($command->execute() / $dataPerPage);

        $startRow = $dataPerPage * ($page - 1);
        $sql = "SELECT *, GetNameProvinceByID(ProvinceID, PageLanguage) AS ProvinceName
            FROM client
            WHERE DeleteDate IS NULL 
            ORDER BY CompanyName,CreateDate DESC
            LIMIT {$startRow},{$dataPerPage}";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $data = $command->queryAll();
        return array(
            'MaxPage' => $maxPage,
            'data' => $data
        );
    }

    public static function CheckEmail($email) {
        $sql = "SELECT *
            FROM client
            WHERE Email = :Email AND DeleteDate IS NULL";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $command->bindParam(':Email', $email);
        return $command->execute();
    }

    public static function getDetailCustomer($ClientID) {
        $sql = "SELECT *, GetNameProvinceByID(ProvinceID, PageLanguage) AS ProvinceName
            FROM client
            WHERE ClientID = :ClientID";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $command->bindParam(':ClientID', $ClientID);
        return $command->queryRow();
    }

    public static function editStatusCustomer($ClientID, $Status) {
        $sql = "UPDATE client
            SET ActiveStatus = :ActiveStatus
            WHERE ClientID = :ClientID;";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $command->bindParam(':ClientID', $ClientID);
        $command->bindParam(':ActiveStatus', $Status);
        return $command->execute();
    }

    public static function insertCustomer($ContactName, $CompanyName, $Address, $ProvinceID, $ZipCode, $Email, $Phone, $Fax, $Mobile, $PageLanguage, $ActiveStatus) {
        $sql = "INSERT INTO client (ContactName, CompanyName, Address, ProvinceID, ZipCode,
            Email, Phone, Fax, Mobile,
            PageLanguage, ActiveStatus, CreateDate)
            Value(:ContactName, :CompanyName, :Address, :ProvinceID, :ZipCode,
            :Email, :Phone, :Fax, :Mobile,
            :PageLanguage, :ActiveStatus, CURDATE());";

        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $command->bindParam(':ContactName', $ContactName);
        $command->bindParam(':CompanyName', $CompanyName);
        $command->bindParam(':Address', $Address);
        $command->bindParam(':ProvinceID', $ProvinceID);
        $command->bindParam(':ZipCode', $ZipCode);
        $command->bindParam(':Email', $Email);
        $command->bindParam(':Phone', $Phone);
        $command->bindParam(':Fax', $Fax);
        $command->bindParam(':Mobile', $Mobile);
        $command->bindParam(':PageLanguage', $PageLanguage);
        $command->bindParam(':ActiveStatus', $ActiveStatus);
        $command->execute();

        $sql = "SELECT ClientID
                FROM client
                WHERE Email = :Email;";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $command->bindParam(':Email', $Email);
        $clientID = $command->queryScalar();

        $token = md5(GM::genUniqKey($Email));

        $sql = "INSERT INTO activatekeys (ActivateKey, ClientID, CreateDate)
                Value(:ActivateKey, :ClientID, CURDATE());";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $command->bindParam(':ActivateKey', $token);
        $command->bindParam(':ClientID', $clientID);
        $command->execute();

        $link = Yii::app()->params['url'] . "index.php?lang={$PageLanguage}&r=Client/Activate&ActivateKey={$token}";
        $sender = 'noreply@avance-th.com';
        $fromname = 'Avance Recruitment (Thailand)';
        $subject = 'Activate your account';

        $body = 'Hello' . $ContactName . ', 
<p>Thank you for register Avance Recruitment (Thailand)</p>
<p>To activate your account <a href="' . $link . '">click</a>. or copy below link and paste on browser</p>
<p>' . $link . '</p>';
        $receiver = $Email;
        if (GM::sendEMail($sender, $fromname, $subject, $body, $receiver)) {
            return true;
        } else {
            return false;
        }
    }

    public static function updateCustomer($ClientID, $ContactName, $CompanyName, $Address, $ProvinceID, $ZipCode, $Phone, $Fax, $Mobile) {

        $sql = "UPDATE client
            SET ContactName = :ContactName, CompanyName = :CompanyName, 
                Address = :Address, ProvinceID = :ProvinceID, ZipCode = :ZipCode,
                Phone = :Phone, Fax = :Fax, Mobile = :Mobile,
                EditDate = CURDATE()
            WHERE ClientID = :ClientID;";

        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $command->bindParam(':ClientID', $ClientID);
        $command->bindParam(':ContactName', $ContactName);
        $command->bindParam(':CompanyName', $CompanyName);
        $command->bindParam(':Address', $Address);
        $command->bindParam(':ProvinceID', $ProvinceID);
        $command->bindParam(':ZipCode', $ZipCode);
        $command->bindParam(':Phone', $Phone);
        $command->bindParam(':Fax', $Fax);
        $command->bindParam(':Mobile', $Mobile);
        return $command->execute();
    }

    public static function deleteCustomer($ClientID) {
        $sql = "UPDATE client
            SET Email = CONCAT(Email,NOW()), DeleteDate = CURDATE()
            WHERE ClientID = :ClientID;";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $command->bindParam(':ClientID', $ClientID);
        return $command->execute();
    }

    public static function getClientIDByAKey($aKey) {
        $sql = "SELECT ClientID
            FROM activatekeys
            WHERE ActivateKey = :AKey;";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $command->bindParam(':AKey', $aKey);
        return $command->queryScalar();
    }
    
    public static function getClientIDByRKey($rKey) {
        $sql = "SELECT ClientID
            FROM retrievekeys
            WHERE RetrieveKey = :RKey;";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $command->bindParam(':RKey', $rKey);
        return $command->queryScalar();
    }

    public static function setPassword($ClientID, $password) {
        $password = md5($password);
        $sql = "UPDATE client
            SET Password = :Password, ActiveStatus = 'A'
            WHERE ClientID = :ClientID;";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $command->bindParam(':ClientID', $ClientID);
        $command->bindParam(':Password', $password);
        if ($command->execute() > 0) {
            $sql = "DELETE FROM activatekeys
                WHERE ClientID = :ClientID;";
            $connection = Yii::app()->db;
            $command = $connection->createCommand($sql);
            $command->bindParam(':ClientID', $ClientID);
            $command->execute();
            
            $sql = "DELETE FROM retrievekeys
                WHERE ClientID = :ClientID;";
            $connection = Yii::app()->db;
            $command = $connection->createCommand($sql);
            $command->bindParam(':ClientID', $ClientID);
            $command->execute();
        }
    }

    public static function Repassword($Email, $PageLanguage) {
        $sql = "SELECT *
            FROM client
            WHERE Email = :Email";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $command->bindParam(':Email', $Email);
        if ($command->execute() > 0) {
            $sql = "SELECT *
                FROM client
                WHERE Email = :Email;";
            $connection = Yii::app()->db;
            $command = $connection->createCommand($sql);
            $command->bindParam(':Email', $Email);
            $client = $command->queryRow();
            $clientID = $client['ClientID'];
            $clientName = $client['ContactName'];

            $sql = "DELETE FROM activatekeys
                WHERE ClientID = :ClientID;";
            $connection = Yii::app()->db;
            $command = $connection->createCommand($sql);
            $command->bindParam(':ClientID', $clientID);
            $command->execute();
            
            $sql = "DELETE FROM retrievekeys
                WHERE ClientID = :ClientID;";
            $connection = Yii::app()->db;
            $command = $connection->createCommand($sql);
            $command->bindParam(':ClientID', $clientID);
            $command->execute();

            $token = md5(GM::genUniqKey($Email));

            $sql = "INSERT INTO  retrievekeys(RetrieveKey, ClientID, CreateDate)
                    Value(:RetrieveKey, :ClientID, CURDATE());";
            $connection = Yii::app()->db;
            $command = $connection->createCommand($sql);
            $command->bindParam(':RetrieveKey', $token);
            $command->bindParam(':ClientID', $clientID);
            $command->execute();

            $link = Yii::app()->params['url'] . "index.php?lang={$PageLanguage}&r=Client/RetrievePassword&RetrieveKey={$token}";
            $sender = 'noreply@avance-th.com';
            $fromname = 'Avance Recruitment (Thailand)';
            $subject = 'Retrieve your password';

            $body = 'Hello ' . $clientName . ', 
    <p>To retrieve your password <a href="' . $link . '">click</a>. or copy below link and paste on browser</p>
    <p>' . $link . '</p>';
            $receiver = $Email;
            if (GM::sendEMail($sender, $fromname, $subject, $body, $receiver)) {
                return "Please check your email.";
            } else {
                return "Can't retrieve password please try again.";
            }
        } else {
            return "Not found email";
        }
    }
    
    public static function checkLogin($Email, $Password){
        $Password = md5($Password);
        
        $sql = "SELECT *
            FROM client
            WHERE Email = :Email AND Password = :Password AND ActiveStatus <> 'S';";
        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $command->bindParam(':Email', $Email);
        $command->bindParam(':Password', $Password);
        if ($command->execute() > 0) {
            return true;
        }
        return false;
    }

//    public static function getDetailCustomerByKeyword($Keyword, $StaffID, $Province, $page, $dataPerPage){
//        
//        $sql = "SELECT *
//            FROM client
//            WHERE DeleteDate IS NULL
//                AND ((LOWER(StaffName) LIKE :Keyword
//                    OR LOWER(WorkPosition) LIKE :Keyword
//                    OR LOWER(WorkExperienceDetail) LIKE :Keyword
//                    OR LOWER(ExpectWorkingPosition) LIKE :Keyword 
//                    OR LOWER(ExpectWorkingPosition) LIKE :Keyword))
//            ORDER BY CreateDate DESC, StaffID DESC";
//        $connection=Yii::app()->db; 
//        $command=$connection->createCommand($sql);
//        $command->bindParam(':StaffID', $StaffID);
//        $command->bindParam(':ProvinceID', $Province);
//        $Keyword = '%'.strtolower($Keyword).'%';
//        $command->bindParam(':Keyword', $Keyword);
//        $maxPage =  ceil($command->execute()/$dataPerPage);
//        
//        $startRow = $dataPerPage*($page-1);
//        $sql = "SELECT *, GetNameProvinceByID(ProvinceID, PageLanguage) AS ProvinceName, GetNameProvinceByID(ExpectWorkingLocation, PageLanguage) AS ExpectLocationName 
//            FROM staff
//            WHERE ActiveStatus IN ({$status}) AND DeleteDate IS NULL
//                AND ((StaffID = :StaffID OR '' = :StaffID) 
//                        AND ((ProvinceID = :ProvinceID OR 0 = :ProvinceID) 
//                            OR (ExpectWorkingLocation = :ProvinceID OR 0 = :ProvinceID))
//                        AND (LOWER(StaffName) LIKE :Keyword
//                            OR LOWER(WorkPosition) LIKE :Keyword
//                            OR LOWER(WorkExperienceDetail) LIKE :Keyword
//                            OR LOWER(ExpectWorkingPosition) LIKE :Keyword 
//                            OR LOWER(ExpectWorkingPosition) LIKE :Keyword))
//            ORDER BY CreateDate DESC, StaffID DESC
//            LIMIT {$startRow},{$dataPerPage}";
//        $connection=Yii::app()->db; 
//        $command=$connection->createCommand($sql);
//        $command->bindParam(':StaffID', $StaffID);
//        $command->bindParam(':ProvinceID', $Province);
//        $command->bindParam(':Keyword', $Keyword);
//        $data =  $command->queryAll();
//        return array(
//            'MaxPage' => $maxPage,
//            'data' => $data
//        );
//        
//    }
}

?>
